# -*- coding: utf-8 -*-
"""
1.从tushare下载历史数据，复权数据，基本面数据.
2.将预测者网每日推送数据入库db

"""
import pandas as pd
import tushare as ts
import os 
import shutil
import datetime

# 添加模块搜索路径
from stock_py.config import config as conf
from stock_py.tools.database import stock_db as db
from stock_py.tools.zip_py import zip_unzip as upzip

#-----------------------------------------
# 每日数据推送地址
push_data_path = conf.g_var.push_data_path
data_into_db_log = conf.g_var.data_into_db_log

#-------测试--------------------------------
def today(has_line=1):
    '''返回今天的日期'''
    t = datetime.datetime.now()
    if has_line:
        return t.strftime("%Y-%m-%d")
    return t.strftime("%Y%m%d")

def take_hist_stock(conn,code,start=None, end=None):
    '''从ts获取指定时间段的历史数据，如果不指定，则全部'''
    df = ts.get_hist_data(code,start=start,end=end)
    # 添加时间列,code列
    df['dates']=df.index
    df['code']=code
    # 选择需要的列
    df2 = df[['code','dates','open','high','close','low','volume','price_change','p_change','ma5','ma10','ma20','v_ma5','v_ma10','v_ma20','turnover']]
    # 入库
    table_name='stock'
    db.df_into_db(conn,tb_name=table_name,df=df2,types='insert')

def take_today_stock(conn):
    '''从ts获取今天的数据，注意，有些数据需要转成万为单位。返回的是全部股票数据'''
    df = ts.get_today_all()
    # 增加时间列
    df['dates'] = today(has_line=1)
    # df[df['code']==code]
    # 成交量转成万手为单位
    df['volume'] = df['volume'].apply(lambda s:round(s/10000,2))
    # 成交额转成万元为单位
    df['amount'] = df['amount'].apply(lambda s:round(s/10000,2))
    # 市场资本额
    df['mktcap'] = df['mktcap'].apply(lambda s:round(s/10000,2))
    # nmc
    df['nmc'] = df['nmc'].apply(lambda s:round(s/10000,2))
    # 选择需要的列入库
    cols = ['code','name','dates','changepercent','trade','open','high','low','settlement','volume','turnoverratio','amount','per','pb','mktcap','nmc']
    df2 = df[cols]
    table_name='stock_now'
    db.df_into_db(conn,tb_name=table_name,df=df2,types='insert')

def take_qfq_stock(conn,code,start=None, end=None):
    '''从ts获取前复权数据，注意，有些数据需要转成万为单位'''
    df = ts.get_h_data(code,start=start,end=end)
    df['dates']=df.index
    df['code']=code
    # volume转成手，和历史数据保持一致
    # amount转成万元为单位
    df['volume'] = df['volume'].apply(lambda s:round(s/100,2))
    df['amount'] = df['amount'].apply(lambda s:round(s/10000,2))
    # 选择需要的列
    cols = ['code','dates','open','high','close','low','volume','amount']
    df2 = df[cols]
    # 入库
    table_name='stock_qfq'
    db.df_into_db(conn,tb_name=table_name,df=df2,types='insert')

def take_basic(conn):
    '''从ts获取基本数据，主要是市盈率市净率等'''
    df = ts.get_stock_basics()
    # df.loc[code]
    df['code']=df.index
    df['dates']=today(has_line=1)
    # 选择需要的列
    cols=['name', 'code', 'dates', 'industry', 'area', 'pe', 'outstanding', 'totals', 'totalAssets', 'liquidAssets', 'fixedAssets', 'reserved', 'reservedPerShare', 'esp', 'bvps', 'pb', 'timeToMarket']
    df2 = df[cols]
    # 入库
    table_name='basic'
    db.df_into_db(conn,tb_name=table_name,df=df2,types='insert')

def get_all_code():
    '''从ts获取今天全部股票代码。原理是先获取今天的全部数据，然后取出代码列即可'''
    # 获取实时行情
    df = ts.get_today_all()
    all_code = list(df.code)
    return all_code


def take_ycz_qx_data(conn,file,last=-300):
    '''将预测者网的全息数据入库。这个函数只是单独针对一个csv数据文件.
    要求file是全路径地址,last:表示取该数据文件的最近n条记录，如果是全部，last=0'''
    data = pd.read_csv(file,encoding='GB18030')
    data.columns = ['code', 'name', 'dates', 'industry', 'notion', 'area', 'open', 'high', 'low', 'close', 'hfq', 'qfq', 'p_change', 'volume', 'amount', 'turnover', 'traded_value', 'total_value', 'up_stop', 'down_stop', 'PE_TTM', 'PS_TTM', 'PC_TTM', 'PB', 'MA_5', 'MA_10', 'MA_20', 'MA_30', 'MA_60', 'ma_fork', 'MACD_DIF', 'MACD_DEA', 'MACD_MACD', 'macd_fork', 'KDJ_K', 'KDJ_D', 'KDJ_J', 'kdj_fork', 'bulin_m', 'bulin_up', 'bulin_d', 'psy', 'psyma', 'rsi1', 'rsi2', 'rsi3', 'p_amplitude', 'volume_ratio']
    # 处理空值和inf等特殊字符串
    data2 = data.applymap(lambda s:str(s))
    data2 = data2.applymap(lambda s:s.replace('nan',''))
    data2 = data2.applymap(lambda s:s.replace('inf',''))
    # 将dates列转成日期后再转成字符串
    # 排序，取最近的400天数据
    data2['tmp'] = data2['dates'].apply(lambda s:pd.to_datetime(s))
    data2['dates'] = data2['tmp'].apply(lambda s: s.strftime("%Y%m%d"))
    del data2['tmp']
    # 取指定多少天的数据，入库
    if data2['dates'].max() >='20151231':  # 退市的就不要了
        data3 = data2.sort_values('dates')[last:]   # 排序后取最近last条记录入库
    else:
        return None
    # 入库
    table_name='stock_qx'
    db.df_into_db(conn,tb_name=table_name,df=data3,types='insert')

def take_ycz_history_qx_data(conn,logger=''):
    '''将预测者网的历史全息数据导入数据库'''
    path1 = r"F:\stock_data\overview-data-sh"
    path2 = r"F:\stock_data\overview-data-sz"
    path = path2
    all_file = os.listdir(path)
    # file = all_file[1]
    for file in all_file:
        if '.csv' not in file:  # 如果是数据文件，而不是文件夹，就入库
            continue
        file = os.path.join(path,file)
        take_ycz_qx_data(conn,file,last=-300)  # 读取数据文件，然后入库
        logger.info(file+' 入库成功')


def read_data_log():
    '''读取日志，看看哪些日期的已经入库了。'''
    data_path=push_data_path  # 数据文件地址
    log = data_into_db_log  # 导数日志名
    log = os.path.join(data_path,log)  # 拼接全路径
    with open(log,'r') as f:
        has_handle_date = f.readlines()
    has_handle_date = [line[:-1] for line in has_handle_date]  # 去掉行尾换行符
    return has_handle_date

def write_data_log(date=''):
    '''将处理的数据日期写入日志文件.date是数据推送的日期'''
    data_path=push_data_path  # 数据文件地址
    log = data_into_db_log  # 导数日志地址
    with open(os.path.join(data_path,log), 'a') as f:
        f.writelines(date+'\n')

def take_ycz_today_qx_data(conn,logger):
    '''查看指定目录下哪些数据文件还没有入库。通过日志文件比对，如果不在日志文件中就是没有入库的'''
    # 先获取日志文件的记录，看看哪些已经入库
    has_handle = read_data_log()
    # 获取当前目录下的所有指定压缩文件
    data_path=push_data_path  # 数据文件地址
    all_zip =[file for file in os.listdir(data_path) if 'overview-push-' in file and '.zip' in file]
    all_zip2 =[os.path.join(data_path,file) for file in all_zip]  # 拼接成全路径
    all_dates = [file.split('-')[2][:8] for file in all_zip]  # 获取数据文件对应的日期
    # 循环判断，如果没有处理就处理
    for short_file,long_file,date in zip(all_zip,all_zip2,all_dates):
        if short_file not in has_handle:
            path = upzip.upzip(long_file)
            data = os.path.join(path,"stock overview.csv")
            # 先删除当前天的数据
            sql = db.delete_today_data(conn,table='stock_qx',date=date)
            logger.info(sql)
            # 数据入库
            take_ycz_qx_data(conn,data,last=0)  # 将数据文件全部入库
            # 写入日志
            write_data_log(date=short_file)
            # 删除解压缩的文件夹
            shutil.rmtree(path)
            logger.info('数据解压，入库成功_'+short_file)

def main():
    # 导入日志
    logger = conf.get_log()
    logger.info('---------------------------------------')
    # 获取数据库连接
    conn = db.connect()
    # 将预测者网的推送数据导入数据库
    take_ycz_today_qx_data(conn,logger)
    logger.info('完成所有数据入库，请核查')
    

if __name__ == '__main__':
    main()


